/*
 * @(#)WorkflowReportDAObject.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.workflow.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.DataSourceFactory;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.web.AbstractSearchForm;
import com.dcivision.workflow.bean.MtmWorkflowProgressUserRecord;
import com.dcivision.workflow.bean.MtmWorkflowStepUserActor;
import com.dcivision.workflow.bean.WorkflowProgress;
import com.dcivision.workflow.bean.WorkflowStep;
import com.dcivision.workflow.web.ListWorkflowMonitoringForm;

/**
 * WorkflowReportDAObject.java
 * 
 * @author Dick Xie
 * @company DCIVision Limited
 * @creation date 2005/4/16
 * @version $Revision: 1.6.20.1 $
 */
public class WorkflowReportDAObject extends WorkflowProgressDAObject {
  
  public WorkflowReportDAObject(SessionContainer sessionContainer, Connection dbConn) {
    super(sessionContainer, dbConn);
  }
  /**
   * Gets a number of all workflow tracks between a time and another time for given searchForm  
   * 
   * @param searchForm The has 
   * @return int The has same parameter for time
   * @throws ApplicationException
   */
  public synchronized int getWorkflowTrackNumberBeforeThisTime(AbstractSearchForm searchForm) throws ApplicationException {
    ListWorkflowMonitoringForm listWorkflowMonitoringForm = (ListWorkflowMonitoringForm)searchForm;
    PreparedStatement preStatCnt = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    int totalNumOfRecord = 0;
    String searchDateFrom = listWorkflowMonitoringForm.getDatePeriodFrom();
    String searchDateTo = listWorkflowMonitoringForm.getDatePeriodTo();
    
    synchronized(dbConn) {
      List trackList = new ArrayList();
      try {
        sqlStatCnt = new StringBuffer("SELECT COUNT(DISTINCT P1.TRACK_ID)  ");
        sqlStatCnt.append("FROM WORKFLOW_PROGRESS P1 INNER JOIN WORKFLOW_STEP S1 ON P1.WORKFLOW_STEP_ID=S1.ID AND S1.STEP_SEQ=? ");
        sqlStatCnt.append("  INNER JOIN WORKFLOW_STEP SX ON S1.WORKFLOW_RECORD_ID=SX.WORKFLOW_RECORD_ID AND SX.STEP_SEQ=? ");
        sqlStatCnt.append("  LEFT JOIN WORKFLOW_PROGRESS PX ON SX.ID=PX.WORKFLOW_STEP_ID AND P1.TRACK_ID=PX.TRACK_ID ");
        sqlStatCnt.append("  ,WORKFLOW_RECORD WR ");
        sqlStatCnt.append("WHERE  S1.WORKFLOW_RECORD_ID=SX.WORKFLOW_RECORD_ID ");
        sqlStatCnt.append("AND  SX.WORKFLOW_RECORD_ID=WR.ID ");
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowGroupID())) {
          sqlStatCnt.append("AND  WR.WORKFLOW_GROUP_ID = ? ");
        }
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          sqlStatCnt.append("AND  WR.ID= ? ");
        }
        sqlStatCnt.append("AND  P1.RECORD_STATUS = S1.RECORD_STATUS ");
        sqlStatCnt.append("AND  S1.RECORD_STATUS = SX.RECORD_STATUS ");
        sqlStatCnt.append("AND  SX.RECORD_STATUS = ? ");
        if(!Utility.isEmpty(searchDateFrom)){
          if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
            sqlStatCnt.append(" AND (P1.CREATE_DATE < ? ) ");
            sqlStatCnt.append(" AND (PX.START_DATE IS NULL OR PX.CREATE_DATE > to_date(?, 'YYYY-MM-DD HH24:MI:SS') ) ");
            sqlStatCnt.append(" AND (P1.STATUS <> ? OR (P1.STATUS=? AND P1.UPDATE_DATE > to_date(?, 'YYYY-MM-DD HH24:MI:SS') )) ");
          } else {
            sqlStatCnt.append(" AND (P1.CREATE_DATE < ?) ");
            sqlStatCnt.append(" AND (PX.CREATE_DATE IS NULL OR PX.CREATE_DATE > ? ) ");
            sqlStatCnt.append(" AND (P1.STATUS <> ? OR (P1.STATUS=? AND P1.UPDATE_DATE > ? )) ");
          }
        }
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        int i = 1;
        this.setPrepareStatement(preStatCnt, i++, WorkflowStep.START_STEP_SEQ_NO);
        this.setPrepareStatement(preStatCnt, i++, WorkflowStep.END_STEP_SEQ_NO);
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowGroupID())) {
          this.setPrepareStatement(preStatCnt, i++, listWorkflowMonitoringForm.getWorkflowGroupID());
        }
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          this.setPrepareStatement(preStatCnt, i++, listWorkflowMonitoringForm.getWorkflowRecordID());
        }
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        if(!Utility.isEmpty(searchDateFrom)){
          this.setPrepareStatement(preStatCnt, i++, searchDateFrom);
          this.setPrepareStatement(preStatCnt, i++, searchDateFrom);
          this.setPrepareStatement(preStatCnt, i++, WorkflowProgress.STATUS_CANCEL);
          this.setPrepareStatement(preStatCnt, i++, WorkflowProgress.STATUS_CANCEL);
          this.setPrepareStatement(preStatCnt, i++, searchDateFrom);
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        log.debug ( sqlStatCnt.toString());
        log.debug ( "getWorkflowTrackNumberBeforeThisTime = " + totalNumOfRecord );
        
        return(totalNumOfRecord);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  /**
   * Gets a number of all created tracks for a given searchForm
   * 
   * @param searchForm
   * @return int
   * @throws ApplicationException
   */
  public synchronized int getCreatedTrackNumber(AbstractSearchForm searchForm) throws ApplicationException {
    ListWorkflowMonitoringForm listWorkflowMonitoringForm = (ListWorkflowMonitoringForm)searchForm;
    PreparedStatement preStatCnt = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    int totalNumOfRecord = 0;
    //  Form the WHERE clause for filtering.
    String searchDateFrom = null;
    String searchDateTo = null;
    String searchField = null;
    searchField = "P.CREATE_DATE";
    searchDateFrom = listWorkflowMonitoringForm.getDatePeriodFrom();
    searchDateTo = listWorkflowMonitoringForm.getDatePeriodTo();
    synchronized(dbConn) {
      List trackList = new ArrayList();
      try {
        sqlStatCnt = new StringBuffer("SELECT COUNT(DISTINCT P.TRACK_ID)  ");
        sqlStatCnt.append("FROM WORKFLOW_PROGRESS P, WORKFLOW_STEP S, WORKFLOW_RECORD WR ");
        sqlStatCnt.append("WHERE  P.WORKFLOW_STEP_ID=S.ID ");
        sqlStatCnt.append("AND  S.WORKFLOW_RECORD_ID=WR.ID ");
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowGroupID())) {
          sqlStatCnt.append("AND  WR.WORKFLOW_GROUP_ID = ? ");
        }
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          sqlStatCnt.append("AND  S.WORKFLOW_RECORD_ID = ? ");
        }
        sqlStatCnt.append("AND S.RECORD_STATUS = ? ");
        sqlStatCnt.append("AND S.STEP_SEQ = ? ");
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          sqlStatCnt.append(" AND ");
          if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= to_date('" + searchDateFrom+"', 'YYYY-MM-DD HH24:MI:SS') AND "+ searchField +" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= to_date('" + searchDateFrom+ "', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
          } else {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= '" + searchDateFrom+"' AND "+ searchField +" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= '" + searchDateFrom+ "') ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
          }
        }
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        int i = 1;
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowGroupID())) {
          this.setPrepareStatement(preStatCnt, i++, listWorkflowMonitoringForm.getWorkflowGroupID());
        }
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          this.setPrepareStatement(preStatCnt, i++, listWorkflowMonitoringForm.getWorkflowRecordID());
        }
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, new Integer(1));
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        log.debug ( sqlStatCnt.toString());
        log.debug ( "getCreatedTrackNumber = " + totalNumOfRecord );
        
        return(totalNumOfRecord);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  /**
   * Gets string of all completed tracks for given searchForm
   * 
   * @param searchForm
   * @return String The String e.g(traskID1,traskID2,traskID3...)
   * @throws ApplicationException
   */
  public synchronized String getCompletedTracks(AbstractSearchForm searchForm) throws ApplicationException {
    ListWorkflowMonitoringForm listWorkflowMonitoringForm = (ListWorkflowMonitoringForm)searchForm;
    PreparedStatement preStatCnt = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    String trackIDs = "";
    //  Form the WHERE clause for filtering.
    String searchDateFrom = null;
    String searchDateTo = null;
    String searchField = null;
    searchField = "P.UPDATE_DATE";
    searchDateFrom = listWorkflowMonitoringForm.getDatePeriodFrom();
    searchDateTo = listWorkflowMonitoringForm.getDatePeriodTo();
    
    synchronized(dbConn) {
      List trackList = new ArrayList();
      try {
        sqlStatCnt = new StringBuffer("SELECT DISTINCT P.TRACK_ID   ");
        sqlStatCnt.append("FROM WORKFLOW_PROGRESS P, WORKFLOW_STEP S, WORKFLOW_RECORD WR ");
        sqlStatCnt.append("WHERE  P.WORKFLOW_STEP_ID=S.ID ");
        sqlStatCnt.append("AND  S.WORKFLOW_RECORD_ID=WR.ID ");
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowGroupID())) {
          sqlStatCnt.append("AND  WR.WORKFLOW_GROUP_ID = ? ");
        }
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          sqlStatCnt.append("AND  S.WORKFLOW_RECORD_ID = ? ");
        }
        sqlStatCnt.append("AND  S.RECORD_STATUS = ? ");
        sqlStatCnt.append("AND  S.STEP_SEQ = ? ");
        sqlStatCnt.append("AND  P.COMPLETE_FLAG = ? ");
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          sqlStatCnt.append("AND ");
          if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= to_date('" + searchDateFrom+"', 'YYYY-MM-DD HH24:MI:SS') AND "+ searchField +" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= to_date('" + searchDateFrom+ "', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
          } else {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= '" + searchDateFrom+"' AND "+ searchField +" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= '" + searchDateFrom+ "') ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
          }
        }
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        int i = 1;
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowGroupID())) {
          this.setPrepareStatement(preStatCnt, i++, listWorkflowMonitoringForm.getWorkflowGroupID());
        }
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          this.setPrepareStatement(preStatCnt, i++, listWorkflowMonitoringForm.getWorkflowRecordID());
        }
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, WorkflowStep.END_STEP_SEQ_NO);
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.TRUE);
        rsCnt = preStatCnt.executeQuery();
        while (rsCnt.next()) {
          trackIDs += ((trackIDs.length()>0)?",":"") + getResultSetInteger(rsCnt, "TRACK_ID");
        }
        log.debug ( sqlStatCnt.toString());
        log.debug ( "getCompletedTrackNumber = " + trackIDs );
        return(trackIDs);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  /**
   * Gets a number of all the completed tracks for given searchForm
   * 
   * @param searchForm
   * @return int
   * @throws ApplicationException
   */
  public synchronized int  getCompletedTrackNumber(AbstractSearchForm searchForm) throws ApplicationException {
    int completedTrackNumber = 0;
    String completedTracks = this.getCompletedTracks(searchForm);
    if (!Utility.isEmpty(completedTracks)) {
      String[] completedTrackStr = completedTracks.split(",");
      completedTrackNumber = completedTrackStr.length;
    }
    return completedTrackNumber;
  }
  /**
   * Gets a number of all the stopped workflow tracks for given searchForm
   * 
   * @param searchForm
   * @return int
   * @throws ApplicationException
   */
  public synchronized int getStoppedWorkflowTrackNumber(AbstractSearchForm searchForm) throws ApplicationException {
    ListWorkflowMonitoringForm listWorkflowMonitoringForm = (ListWorkflowMonitoringForm)searchForm;
    PreparedStatement preStatCnt = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    int totalNumOfRecord = 0;
    //  Form the WHERE clause for filtering.
    String searchDateFrom = null;
    String searchDateTo = null;
    String searchField = null;
    searchField = "P.UPDATE_DATE";
    searchDateFrom = listWorkflowMonitoringForm.getDatePeriodFrom();
    searchDateTo = listWorkflowMonitoringForm.getDatePeriodTo();
    synchronized(dbConn) {
      List trackList = new ArrayList();
      try {
        sqlStatCnt = new StringBuffer("SELECT COUNT(DISTINCT P.TRACK_ID)  ");
        sqlStatCnt.append("FROM WORKFLOW_PROGRESS P, WORKFLOW_STEP S, WORKFLOW_RECORD WR ");
        sqlStatCnt.append("WHERE  P.WORKFLOW_STEP_ID=S.ID ");
        sqlStatCnt.append("AND  S.WORKFLOW_RECORD_ID=WR.ID ");
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowGroupID())) {
          sqlStatCnt.append("AND  WR.WORKFLOW_GROUP_ID = ? ");
        }
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          sqlStatCnt.append("AND  S.WORKFLOW_RECORD_ID = ? ");
        }
        sqlStatCnt.append("AND  S.RECORD_STATUS = ? ");
        sqlStatCnt.append("AND     P.STATUS = ? ");
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          sqlStatCnt.append(" AND ");
          if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= to_date('" + searchDateFrom+"', 'YYYY-MM-DD HH24:MI:SS') AND "+ searchField +" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= to_date('" + searchDateFrom+ "', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
          } else {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= '" + searchDateFrom+"' AND "+ searchField +" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= '" + searchDateFrom+ "') ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
          }
        }
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        int i = 1;
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowGroupID())) {
          this.setPrepareStatement(preStatCnt, i++, listWorkflowMonitoringForm.getWorkflowGroupID());
        }
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          this.setPrepareStatement(preStatCnt, i++, listWorkflowMonitoringForm.getWorkflowRecordID());
        }
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, WorkflowProgress.STATUS_CANCEL);
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        log.debug ( sqlStatCnt.toString());
        log.debug ( "getStoppedWorkflowTrackNumber = " + totalNumOfRecord );
        
        return(totalNumOfRecord);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  /**
   * Gets a list of workflow track by workflow record for given searchForm
   * 
   * @param searchForm
   * @return list The String[] object
   * @throws ApplicationException
   */
  public synchronized List getWorkflowTrackListByWorkflowRecord3(AbstractSearchForm searchForm) throws ApplicationException {
    ListWorkflowMonitoringForm listWorkflowMonitoringForm = (ListWorkflowMonitoringForm)searchForm;
    String completeTrackIDs = this.getCompletedTracks(searchForm);
    if(Utility.isEmpty(completeTrackIDs)) return new ArrayList();
    
    int rows = 0;
    Statement stat = null;
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    String searchDateFrom = null;
    String searchDateTo = null;
    String searchField = null;
    searchField = "WP.UPDATE_DATE";
    searchDateFrom = listWorkflowMonitoringForm.getDatePeriodFrom();
    searchDateTo = listWorkflowMonitoringForm.getDatePeriodTo();
    synchronized(dbConn) {
      try {
        sqlStat = new StringBuffer("DROP TABLE ##TMP_TABLE\n");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        try{ rows = preStat.executeUpdate(); }catch(SQLException ignore){}
        log.debug("##TMP_TABLE deleted = "+rows);
        
        sqlStat = new StringBuffer();
        sqlStat.append("SELECT WP.ID, WP.TRACK_ID,WP.CREATE_DATE,WP.UPDATE_DATE \n");
        sqlStat.append("INTO ##TMP_TABLE \n");
        sqlStat.append("FROM WORKFLOW_PROGRESS WP, WORKFLOW_STEP S, MTM_WF_STAGE_WF_STEP M, WORKFLOW_STAGE T \n");
        sqlStat.append("WHERE  WP.WORKFLOW_STEP_ID=S.ID \n");
        sqlStat.append("AND S.ID=M.WORKFLOW_STEP_ID \n");
        sqlStat.append("AND M.RECORD_STATUS=? \n");
        sqlStat.append("AND M.WORKFLOW_STAGE_ID=T.ID \n");
        //      sqlStat.append("AND WP.TRACK_ID = ? \n");
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          sqlStat.append("AND S.WORKFLOW_RECORD_ID = ? ");
        }
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          sqlStat.append(" AND ");
          if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
            sqlStat.append(" ("+ searchField +" >= ? AND "+ searchField +" < ?) ");
          else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
            sqlStat.append(" ("+searchField +" >= ?) ");
          else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
            sqlStat.append(" (" +searchField+" < ?) ");
        }
        sqlStat.append("AND WP.COMPLETE_FLAG = ? ");
        log.debug (sqlStat.toString());
        preStat = dbConn.prepareStatement(sqlStat.toString());
        
        int i = 1;
        this.setPrepareStatement(preStat, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          this.setPrepareStatement(preStat, i++, listWorkflowMonitoringForm.getWorkflowRecordID());
        }
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) ) {
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(searchDateFrom));
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(TextUtility.dbDateAdder(searchDateTo,1)));
          } else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) ) {
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(searchDateFrom));
          } else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) ) {
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(TextUtility.dbDateAdder(searchDateTo,1)));
          }
        }
        this.setPrepareStatement(preStat, i++, GlobalConstant.TRUE);
        rows = preStat.executeUpdate();
        log.debug("Number rows in ##TMP_TABLE = "+rows);
        
        sqlStatCnt = new StringBuffer("SELECT MIN(ALL_DURATION) AS MIN_ALL, MAX(ALL_DURATION) AS MAX_ALL, AVG(ALL_DURATION) AS AVG_ALL,  MIN(ISNULL(ACTUAL_DURATION, ALL_DURATION)) AS MIN_ACTUAL, MAX(ISNULL(ACTUAL_DURATION, ALL_DURATION)) AS MAX_ACTUAL, AVG(ISNULL(ACTUAL_DURATION, ALL_DURATION)) AS AVG_ACTUAL FROM ( \n");
        sqlStatCnt.append("SELECT TEMP1.TRACK_ID, TEMP1.LARGEST_DIFF AS ALL_DURATION, TEMP2.HOLES_SUM, TEMP1.LARGEST_DIFF-TEMP2.HOLES_SUM AS ACTUAL_DURATION \n");
        sqlStatCnt.append("FROM \n");
        sqlStatCnt.append("(SELECT WP.TRACK_ID, DATEDIFF([SECOND], MIN(WP.CREATE_DATE), MAX(WP.UPDATE_DATE)) AS LARGEST_DIFF \n");
        sqlStatCnt.append("FROM ##TMP_TABLE WP \n");
        sqlStatCnt.append("GROUP BY WP.TRACK_ID) TEMP1 \n");
        sqlStatCnt.append("LEFT JOIN  \n");
        sqlStatCnt.append("(SELECT TRACK_ID, SUM(DATEDIFF([SECOND], HOLE_START, HOLE_END)) AS HOLES_SUM FROM ( \n");
        sqlStatCnt.append("  SELECT T2.TRACK_ID, MAX(T1.UPDATE_DATE) AS HOLE_START, T2.CREATE_DATE AS HOLE_END \n");
        sqlStatCnt.append("   FROM ##TMP_TABLE T1, ##TMP_TABLE T2  \n");
        sqlStatCnt.append("  WHERE T1.TRACK_ID=T2.TRACK_ID AND T1.ID <> T2.ID \n");
        sqlStatCnt.append("  AND NOT  EXISTS (SELECT 'X' FROM ##TMP_TABLE T3 WHERE T3.TRACK_ID=T2.TRACK_ID AND T3.UPDATE_DATE > T2.CREATE_DATE AND T3.CREATE_DATE < T1.UPDATE_DATE)  \n");
        sqlStatCnt.append("  AND NOT  EXISTS (SELECT 'X' FROM ##TMP_TABLE T4 WHERE T4.TRACK_ID=T2.TRACK_ID AND T4.CREATE_DATE < T2.CREATE_DATE AND T4.UPDATE_DATE > T1.UPDATE_DATE)  \n");
        sqlStatCnt.append("  GROUP BY T2.TRACK_ID, T2.CREATE_DATE \n");
        sqlStatCnt.append(") DIFF_TABLE \n");
        sqlStatCnt.append("GROUP BY TRACK_ID) TEMP2 \n");
        sqlStatCnt.append("ON TEMP1.TRACK_ID=TEMP2.TRACK_ID \n");
        sqlStatCnt.append(") TEMP3 \n");
        log.debug(sqlStatCnt.toString());
        stat = dbConn.createStatement();
        rs = stat.executeQuery(sqlStatCnt.toString());
        //      rs = preStatCnt.executeQuery();
        while (rs.next()) {
          String values[] = new String[]{
              String.valueOf(getResultSetFloat(rs, "MIN_ALL")),
              String.valueOf(getResultSetFloat(rs, "MAX_ALL")),
              String.valueOf(getResultSetFloat(rs, "AVG_ALL")),
              String.valueOf(getResultSetFloat(rs, "MIN_ACTUAL")),
              String.valueOf(getResultSetFloat(rs, "MAX_ACTUAL")),
              String.valueOf(getResultSetFloat(rs, "AVG_ACTUAL"))
          };
          result.add(values);
        }
        
        sqlStat = new StringBuffer("DROP TABLE ##TMP_TABLE\n");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        try{ rows = preStat.executeUpdate(); }catch(SQLException ignore){}
        log.debug("##TMP_TABLE deleted = "+rows);
        
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { stat.close(); } catch (Exception ignore) {} finally { stat = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Gets a list of workflow track by workflow record for given searchForm
   * 
   * @param searchForm
   * @return list The String object
   * @throws ApplicationException
   */
  public synchronized List getWorkflowTrackListByWorkflowRecord(AbstractSearchForm searchForm) throws ApplicationException {
    String completeTrackIDs = this.getCompletedTracks(searchForm);
    PreparedStatement preStatCnt = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    List trackForExeTimeList  = new ArrayList();
    if(Utility.isEmpty(completeTrackIDs)) {
      return trackForExeTimeList;
    }
    synchronized(dbConn) {
      List trackList = new ArrayList();
      try {
        sqlStatCnt.append("  SELECT P.TRACK_ID, MAX(P.UPDATE_DATE) AS MAX_UPDATE , MIN(P.CREATE_DATE) AS MIN_CREATE \n");
        sqlStatCnt.append("FROM  WORKFLOW_PROGRESS P \n");
        sqlStatCnt.append(" WHERE   P.TRACK_ID  IN (" + completeTrackIDs + ") ");
        sqlStatCnt.append("  GROUP BY P.TRACK_ID \n");
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        rsCnt = preStatCnt.executeQuery();
        while (rsCnt.next()) {
          String currentTime = this.diffCompleteTimes(getResultSetTimestamp(rsCnt, "MAX_UPDATE"), getResultSetTimestamp(rsCnt, "MIN_CREATE"));
          trackForExeTimeList.add(currentTime);
        }
        log.debug ( sqlStatCnt.toString());
        log.debug ( "getCompletedTrackNumber = " + trackForExeTimeList.size() );
        return(trackForExeTimeList);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
    /*
    ListWorkflowMonitoringForm listWorkflowMonitoringForm = (ListWorkflowMonitoringForm)searchForm;
    String completeTrackIDs = this.getCompletedTracks(searchForm);
    if(Utility.isEmpty(completeTrackIDs)) return new ArrayList();
    
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    String searchDateFrom = null;
    String searchDateTo = null;
    String searchField = null;
    searchField = "P.UPDATE_DATE";
    searchDateFrom = listWorkflowMonitoringForm.getDatePeriodFrom();
    searchDateTo = listWorkflowMonitoringForm.getDatePeriodTo();
    synchronized(dbConn) {
      List trackList = new ArrayList();
      try {
        sqlStatCnt = new StringBuffer("SELECT P.TRACK_ID, MAX(P.UPDATE_DATE) AS MAX_UPDATE,  MIN(P.CREATE_DATE) AS MIN_CREATE ");
        sqlStatCnt.append("FROM  WORKFLOW_PROGRESS P INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STEP S ON P.WORKFLOW_STEP_ID = S.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_STAGE_WF_STEP M ON S.ID = M.WORKFLOW_STEP_ID INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STAGE T ON M.WORKFLOW_STAGE_ID = T.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_STEP_USER_ACTOR A ON ");
        sqlStatCnt.append(" S.ID = A.WORKFLOW_STEP_ID  ");
        sqlStatCnt.append("WHERE S.RECORD_STATUS = ? ");
        sqlStatCnt.append(" AND T.RECORD_STATUS = ? ");
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          sqlStatCnt.append("AND  S.WORKFLOW_RECORD_ID = ? ");
        }
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          sqlStatCnt.append(" AND ");
          if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= to_date('" + searchDateFrom+"', 'YYYY-MM-DD HH24:MI:SS') AND "+ searchField +" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= to_date('" + searchDateFrom+ "', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
          } else {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= '" + searchDateFrom+"' AND "+ searchField +" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= '" + searchDateFrom+ "') ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
          }
        }
        sqlStatCnt.append(" AND P.TRACK_ID  IN (" + completeTrackIDs + ") ");
        sqlStatCnt.append(" GROUP BY P.TRACK_ID ");
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        log.debug ( sqlStatCnt.toString());
        
        int i = 1;
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          this.setPrepareStatement(preStatCnt, i++, listWorkflowMonitoringForm.getWorkflowRecordID());
        }
        rs = preStatCnt.executeQuery();
        while (rs.next()) {
          String currentTime = this.diffCompleteTimes(getResultSetTimestamp(rs, "MAX_UPDATE"), getResultSetTimestamp(rs, "MIN_CREATE"));
          result.add(currentTime);
        }  
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
    */
  }
  /**
   * Gets a list of workflow track by owner for given searchForm
   * 
   * @param searchForm
   * @return list The String[] object
   * @throws ApplicationException
   */
  public synchronized List getWorkflowTrackListByOwner3(AbstractSearchForm searchForm) throws ApplicationException {
    ListWorkflowMonitoringForm listWorkflowMonitoringForm = (ListWorkflowMonitoringForm)searchForm;
    String completeTrackIDs = this.getCompletedTracks(searchForm);
    if(Utility.isEmpty(completeTrackIDs)) return new ArrayList();
    
    int rows = 0;
    Statement stat = null;
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    String searchDateFrom = null;
    String searchDateTo = null;
    String searchField = null;
    searchField = "WP.UPDATE_DATE";
    searchDateFrom = listWorkflowMonitoringForm.getDatePeriodFrom();
    searchDateTo = listWorkflowMonitoringForm.getDatePeriodTo();
    synchronized(dbConn) {
      try {
        sqlStat = new StringBuffer("DROP TABLE ##TMP_TABLE\n");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        try{ rows = preStat.executeUpdate(); }catch(SQLException ignore){}
        log.debug("##TMP_TABLE deleted = "+rows);
        
        sqlStat = new StringBuffer();
        
        sqlStat.append("SELECT WP.ID, T.ID AS STAGE_ID, T.STAGE_NAME, R.ID AS ROLE_ID, R.ROLE_NAME, WP.TRACK_ID, WP.CREATE_DATE, WP.UPDATE_DATE \n");
        sqlStat.append("INTO ##TMP_TABLE \n");
        sqlStat.append("FROM WORKFLOW_PROGRESS WP, WORKFLOW_STEP S, MTM_WF_STAGE_WF_STEP M, WORKFLOW_STAGE T, \n");
        sqlStat.append("  MTM_WF_STEP_USER_ACTOR A, USER_ROLE R \n");
        sqlStat.append("WHERE  WP.WORKFLOW_STEP_ID=S.ID  \n");
        sqlStat.append("AND S.ID=M.WORKFLOW_STEP_ID  \n");
        sqlStat.append("AND M.WORKFLOW_STAGE_ID=T.ID  \n");
        sqlStat.append("AND S.ID=A.WORKFLOW_STEP_ID \n");
        sqlStat.append("AND A.ACTOR_TYPE=? \n");
        sqlStat.append("AND A.ACTOR_ID=R.ID \n");
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          sqlStat.append("AND S.WORKFLOW_RECORD_ID = ? ");
        }
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          sqlStat.append(" AND ");
          if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
            sqlStat.append(" ("+ searchField +" >= ? AND "+ searchField +" < ?) ");
          else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
            sqlStat.append(" ("+searchField +" >= ?) ");
          else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
            sqlStat.append(" (" +searchField+" < ?) ");
        }
        sqlStat.append("AND WP.COMPLETE_FLAG = ? ");
        sqlStat.append("--GROUP BY T.ID, T.STAGE_NAME, R.ID, R.ROLE_NAME \n");
        sqlStat.append("ORDER BY T.ID, T.STAGE_NAME, R.ROLE_NAME, WP.CREATE_DATE, WP.UPDATE_DATE \n");
        
        log.debug (sqlStat.toString());
        preStat = dbConn.prepareStatement(sqlStat.toString());
        
        int i = 1;
        this.setPrepareStatement(preStat, i++, MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE);
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          this.setPrepareStatement(preStat, i++, listWorkflowMonitoringForm.getWorkflowRecordID());
        }
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) ) {
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(searchDateFrom));
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(TextUtility.dbDateAdder(searchDateTo,1)));
          } else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) ) {
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(searchDateFrom));
          } else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) ) {
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(TextUtility.dbDateAdder(searchDateTo,1)));
          }
        }
        this.setPrepareStatement(preStat, i++, GlobalConstant.TRUE);
        rows = preStat.executeUpdate();
        log.debug("Number rows in ##TMP_TABLE = "+rows);
        
        sqlStatCnt = new StringBuffer("SELECT STAGE_ID, STAGE_NAME, ROLE_ID, ROLE_NAME, MIN(ALL_DURATION) AS MIN_ALL, MAX(ALL_DURATION) AS MAX_ALL, AVG(ALL_DURATION) AS AVG_ALL,  MIN(ISNULL(ACTUAL_DURATION, ALL_DURATION)) AS MIN_ACTUAL, MAX(ISNULL(ACTUAL_DURATION, ALL_DURATION)) AS MAX_ACTUAL, AVG(ISNULL(ACTUAL_DURATION, ALL_DURATION)) AS AVG_ACTUAL FROM (  \n");
        sqlStatCnt.append("SELECT TEMP1.STAGE_ID, TEMP1.STAGE_NAME, TEMP1.ROLE_ID, TEMP1.ROLE_NAME, TEMP1.TRACK_ID, TEMP1.LARGEST_DIFF AS ALL_DURATION, TEMP2.HOLES_SUM, TEMP1.LARGEST_DIFF-TEMP2.HOLES_SUM AS ACTUAL_DURATION  \n");
        sqlStatCnt.append("FROM  \n");
        sqlStatCnt.append("(SELECT WP.STAGE_ID, WP.STAGE_NAME, WP.ROLE_ID, WP.ROLE_NAME, WP.TRACK_ID, DATEDIFF([SECOND], MIN(WP.CREATE_DATE), MAX(WP.UPDATE_DATE)) AS LARGEST_DIFF  \n");
        sqlStatCnt.append("FROM ##TMP_TABLE WP \n");
        sqlStatCnt.append("GROUP BY WP.STAGE_ID, WP.STAGE_NAME, WP.ROLE_ID, WP.ROLE_NAME, WP.TRACK_ID) TEMP1 \n");
        sqlStatCnt.append("LEFT JOIN  \n");
        sqlStatCnt.append("(SELECT  STAGE_ID, STAGE_NAME, ROLE_ID, ROLE_NAME, TRACK_ID, SUM(DATEDIFF([SECOND], HOLE_START, HOLE_END)) AS HOLES_SUM FROM (  \n");
        sqlStatCnt.append("  SELECT T2.STAGE_ID, T2.STAGE_NAME, T2.ROLE_ID, T2.ROLE_NAME, T2.TRACK_ID, MAX(T1.UPDATE_DATE) AS HOLE_START, T2.CREATE_DATE AS HOLE_END  \n");
        sqlStatCnt.append("   FROM ##TMP_TABLE T1,  ##TMP_TABLE T2   \n");
        sqlStatCnt.append("  WHERE T1.STAGE_ID=T2.STAGE_ID AND T1.ROLE_ID=T2.ROLE_ID AND T1.TRACK_ID=T2.TRACK_ID AND T1.ID <> T2.ID  \n");
        sqlStatCnt.append("  AND NOT  EXISTS (SELECT 'X' FROM ##TMP_TABLE T3 WHERE T3.STAGE_ID=T2.STAGE_ID AND T3.ROLE_ID=T2.ROLE_ID AND T3.TRACK_ID=T2.TRACK_ID AND T3.UPDATE_DATE > T2.CREATE_DATE AND T3.CREATE_DATE < T1.UPDATE_DATE)   \n");
        sqlStatCnt.append("  AND NOT  EXISTS (SELECT 'X' FROM ##TMP_TABLE T4 WHERE T4.STAGE_ID=T2.STAGE_ID AND T4.ROLE_ID=T2.ROLE_ID AND T4.TRACK_ID=T2.TRACK_ID AND T4.CREATE_DATE < T2.CREATE_DATE AND T4.UPDATE_DATE > T1.UPDATE_DATE)   \n");
        sqlStatCnt.append("  GROUP BY T2.STAGE_ID, T2.STAGE_NAME, T2.ROLE_ID, T2.ROLE_NAME, T2.TRACK_ID, T2.CREATE_DATE  \n");
        sqlStatCnt.append(") DIFF_TABLE  \n");
        sqlStatCnt.append("GROUP BY STAGE_ID, STAGE_NAME, ROLE_ID, ROLE_NAME, TRACK_ID) TEMP2  \n");
        sqlStatCnt.append("ON TEMP1.STAGE_ID=TEMP2.STAGE_ID  \n");
        sqlStatCnt.append("AND TEMP1.ROLE_ID=TEMP2.ROLE_ID \n");
        sqlStatCnt.append("AND TEMP1.TRACK_ID=TEMP2.TRACK_ID \n");
        sqlStatCnt.append(") TEMP3  \n");
        sqlStatCnt.append("GROUP BY STAGE_ID, STAGE_NAME, ROLE_ID, ROLE_NAME \n");
        log.debug(sqlStatCnt.toString());
        stat = dbConn.createStatement();
        rs = stat.executeQuery(sqlStatCnt.toString());
        //      rs = preStatCnt.executeQuery();
        while (rs.next()) {
          String values[] = new String[]{getResultSetString(rs, "STAGE_NAME"),
              getResultSetString(rs, "ROLE_NAME"),
              String.valueOf(getResultSetFloat(rs, "MIN_ALL")),
              String.valueOf(getResultSetFloat(rs, "MAX_ALL")),
              String.valueOf(getResultSetFloat(rs, "AVG_ALL")),
              String.valueOf(getResultSetFloat(rs, "MIN_ACTUAL")),
              String.valueOf(getResultSetFloat(rs, "MAX_ACTUAL")),
              String.valueOf(getResultSetFloat(rs, "AVG_ACTUAL"))
          };
          result.add(values);
        }
        
        sqlStat = new StringBuffer("DROP TABLE ##TMP_TABLE\n");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        try{ rows = preStat.executeUpdate(); }catch(SQLException ignore){}
        log.debug("##TMP_TABLE deleted = "+rows);
        
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { stat.close(); } catch (Exception ignore) {} finally { stat = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Gets a list of workflow track by owner for given searchForm
   * 
   * @param searchForm
   * @return list The String object
   * @throws ApplicationException
   */
  public synchronized List getWorkflowTrackListByOwner(AbstractSearchForm searchForm) throws ApplicationException {
    ListWorkflowMonitoringForm listWorkflowMonitoringForm = (ListWorkflowMonitoringForm)searchForm;
    String completeTrackIDs = this.getCompletedTracks(searchForm);
    if(Utility.isEmpty(completeTrackIDs)) return new ArrayList();
    
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    //    Form the WHERE clause for filtering.
    String searchDateFrom = null;
    String searchDateTo = null;
    String searchField = null;
    searchField = "P.UPDATE_DATE";
    searchDateFrom = listWorkflowMonitoringForm.getDatePeriodFrom();
    searchDateTo = listWorkflowMonitoringForm.getDatePeriodTo();
    synchronized(dbConn) {
      try {
        sqlStatCnt = new StringBuffer("SELECT T.ID, T.STAGE_NAME, A.ACTOR_ID , A.ACTOR_TYPE, T.DURATION, P.TRACK_ID, ");
        sqlStatCnt.append(" MAX(P.UPDATE_DATE) AS MAX_UPDATE,  MIN(P.CREATE_DATE) AS MIN_CREATE ");
        sqlStatCnt.append("FROM  WORKFLOW_PROGRESS P INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STEP S ON P.WORKFLOW_STEP_ID = S.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_STAGE_WF_STEP M ON S.ID = M.WORKFLOW_STEP_ID INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STAGE T ON M.WORKFLOW_STAGE_ID = T.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_STEP_USER_ACTOR A ON ");
        sqlStatCnt.append(" S.ID = A.WORKFLOW_STEP_ID  ");
        sqlStatCnt.append("WHERE S.RECORD_STATUS = ? ");
        sqlStatCnt.append(" AND T.RECORD_STATUS = ? ");
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          sqlStatCnt.append("AND  S.WORKFLOW_RECORD_ID = ? ");
        }
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          sqlStatCnt.append(" AND ");
          if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= to_date('" + searchDateFrom+"', 'YYYY-MM-DD HH24:MI:SS') AND "+ searchField +" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= to_date('" + searchDateFrom+ "', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
          } else {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= '" + searchDateFrom+"' AND "+ searchField +" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= '" + searchDateFrom+ "') ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
          }
        }
        sqlStatCnt.append("   AND     P.TRACK_ID  IN (" + completeTrackIDs + ") ");
        sqlStatCnt.append(" GROUP BY  T.ID, T.STAGE_NAME, T.DURATION, A.ACTOR_ID, A.ACTOR_TYPE, P.TRACK_ID ");
        
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        log.debug ( sqlStatCnt.toString());
        
        int i = 1;
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          this.setPrepareStatement(preStatCnt, i++, listWorkflowMonitoringForm.getWorkflowRecordID());
        }
        rs = preStatCnt.executeQuery();
        while (rs.next()) {
          String currentTime = this.diffCompleteTimes(getResultSetTimestamp(rs, "MAX_UPDATE"), getResultSetTimestamp(rs, "MIN_CREATE"));
          String values[] = new String[]{String.valueOf(getResultSetInteger(rs, "TRACK_ID")),
              String.valueOf(getResultSetInteger(rs, "ID")),
              String.valueOf(getResultSetInteger(rs, "ACTOR_ID")),
              getResultSetString(rs, "STAGE_NAME"),
              getResultSetString(rs, "ACTOR_TYPE"),
              currentTime
          };
          result.add(values);
        }
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  /**
   * Gets a list of workflow track by action taker for given searchForm
   * 
   * @param searchForm
   * @return list The String[] object
   * @throws ApplicationException
   */
  public synchronized List getWorkflowTrackListByActionTaker3(AbstractSearchForm searchForm) throws ApplicationException {
    ListWorkflowMonitoringForm listWorkflowMonitoringForm = (ListWorkflowMonitoringForm)searchForm;
    String completeTrackIDs = this.getCompletedTracks(searchForm);
    if(Utility.isEmpty(completeTrackIDs)) return new ArrayList();
    
    int rows = 0;
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    String searchDateFrom = null;
    String searchDateTo = null;
    String searchField = null;
    searchField = "WP.UPDATE_DATE";
    searchDateFrom = listWorkflowMonitoringForm.getDatePeriodFrom();
    searchDateTo = listWorkflowMonitoringForm.getDatePeriodTo();
    synchronized(dbConn) {
      try {
        sqlStat = new StringBuffer("DROP TABLE ##TMP_TABLE\n");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        try{ rows = preStat.executeUpdate(); }catch(SQLException ignore){}
        log.debug("##TMP_TABLE deleted = "+rows);
        
        sqlStat = new StringBuffer();
        
        sqlStat.append("SELECT WP.ID, M.RECORD_STATUS, T.ID AS STAGE_ID, T.STAGE_NAME, U.ID AS USER_ID, U.FULL_NAME, WP.TRACK_ID, WP.CREATE_DATE, WP.UPDATE_DATE \n");
        sqlStat.append("INTO ##TMP_TABLE \n");
        sqlStat.append("FROM WORKFLOW_PROGRESS WP, WORKFLOW_STEP S, MTM_WF_STAGE_WF_STEP M, WORKFLOW_STAGE T, \n");
        sqlStat.append("  MTM_WF_PROGRESS_USER_RECORD PUR, USER_RECORD U \n");
        sqlStat.append("WHERE  WP.WORKFLOW_STEP_ID=S.ID  \n");
        sqlStat.append("AND S.ID=M.WORKFLOW_STEP_ID  \n");
        sqlStat.append("AND M.WORKFLOW_STAGE_ID=T.ID  \n");
        sqlStat.append("AND PUR.WORKFLOW_PROGRESS_ID=WP.ID \n");
        sqlStat.append("AND PUR.USER_RECORD_ID=U.ID \n");
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          sqlStat.append("AND S.WORKFLOW_RECORD_ID = ? ");
        }
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          sqlStat.append(" AND ");
          if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
            sqlStat.append(" ("+ searchField +" >= ? AND "+ searchField +" < ?) ");
          else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
            sqlStat.append(" ("+searchField +" >= ?) ");
          else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
            sqlStat.append(" (" +searchField+" < ?) ");
        }
        sqlStat.append("AND WP.COMPLETE_FLAG = ? ");
        sqlStat.append("--GROUP BY T.ID, T.STAGE_NAME, U.ID, U.FULL_NAME \n");
        sqlStat.append("ORDER BY T.ID, T.STAGE_NAME, U.FULL_NAME, WP.CREATE_DATE, WP.UPDATE_DATE \n");
        
        log.debug (sqlStat.toString());
        preStat = dbConn.prepareStatement(sqlStat.toString());
        
        int i = 1;
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          this.setPrepareStatement(preStat, i++, listWorkflowMonitoringForm.getWorkflowRecordID());
        }
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) ) {
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(searchDateFrom));
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(TextUtility.dbDateAdder(searchDateTo,1)));
          } else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) ) {
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(searchDateFrom));
          } else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) ) {
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(TextUtility.dbDateAdder(searchDateTo,1)));
          }
        }
        this.setPrepareStatement(preStat, i++, GlobalConstant.TRUE);
        rows = preStat.executeUpdate();
        log.debug("Number rows in ##TMP_TABLE = "+rows);
        
        sqlStatCnt = new StringBuffer("SELECT STAGE_ID, STAGE_NAME, USER_ID, FULL_NAME, MIN(ALL_DURATION) AS MIN_ALL, MAX(ALL_DURATION) AS MAX_ALL, AVG(ALL_DURATION) AS AVG_ALL,  MIN(ISNULL(ACTUAL_DURATION, ALL_DURATION)) AS MIN_ACTUAL, MAX(ISNULL(ACTUAL_DURATION, ALL_DURATION)) AS MAX_ACTUAL, AVG(ISNULL(ACTUAL_DURATION, ALL_DURATION)) AS AVG_ACTUAL FROM (  \n");
        sqlStatCnt.append("SELECT TEMP1.STAGE_ID, TEMP1.STAGE_NAME, TEMP1.USER_ID, TEMP1.FULL_NAME, TEMP1.TRACK_ID, TEMP1.LARGEST_DIFF AS ALL_DURATION, TEMP2.HOLES_SUM, TEMP1.LARGEST_DIFF-TEMP2.HOLES_SUM AS ACTUAL_DURATION  \n");
        sqlStatCnt.append("FROM  \n");
        sqlStatCnt.append("(SELECT WP.STAGE_ID, WP.STAGE_NAME, WP.USER_ID, WP.FULL_NAME, WP.TRACK_ID, DATEDIFF([SECOND], MIN(WP.CREATE_DATE), MAX(WP.UPDATE_DATE)) AS LARGEST_DIFF  \n");
        sqlStatCnt.append("FROM ##TMP_TABLE WP \n");
        sqlStatCnt.append("GROUP BY WP.STAGE_ID, WP.STAGE_NAME, WP.USER_ID, WP.FULL_NAME, WP.TRACK_ID) TEMP1 \n");
        sqlStatCnt.append("LEFT JOIN  \n");
        sqlStatCnt.append("(SELECT  STAGE_ID, STAGE_NAME, USER_ID, FULL_NAME, TRACK_ID, SUM(DATEDIFF([SECOND], HOLE_START, HOLE_END)) AS HOLES_SUM FROM (  \n");
        sqlStatCnt.append("  SELECT T2.STAGE_ID, T2.STAGE_NAME, T2.USER_ID, T2.FULL_NAME, T2.TRACK_ID \n");
        sqlStatCnt.append(", MAX(T1.UPDATE_DATE) AS HOLE_START, T2.CREATE_DATE AS HOLE_END   \n");
        sqlStatCnt.append("   FROM ##TMP_TABLE T1,  ##TMP_TABLE T2    \n");
        sqlStatCnt.append("  WHERE T1.STAGE_ID=T2.STAGE_ID AND T1.USER_ID=T2.USER_ID AND T1.TRACK_ID=T2.TRACK_ID AND T1.ID <> T2.ID   \n");
        sqlStatCnt.append("  AND T1.RECORD_STATUS=? AND T2.RECORD_STATUS=? AND T1.RECORD_STATUS=T2.RECORD_STATUS \n");
        sqlStatCnt.append("  AND NOT  EXISTS (SELECT 'X' FROM ##TMP_TABLE T3 WHERE T3.RECORD_STATUS=? AND T3.STAGE_ID=T2.STAGE_ID AND T3.USER_ID=T2.USER_ID AND T3.TRACK_ID=T2.TRACK_ID AND T3.UPDATE_DATE > T2.CREATE_DATE AND T3.CREATE_DATE < T1.UPDATE_DATE)    \n");
        sqlStatCnt.append("  AND NOT  EXISTS (SELECT 'X' FROM ##TMP_TABLE T4 WHERE T4.RECORD_STATUS=? AND T4.STAGE_ID=T2.STAGE_ID AND T4.USER_ID=T2.USER_ID AND T4.TRACK_ID=T2.TRACK_ID AND T4.CREATE_DATE < T2.CREATE_DATE AND T4.UPDATE_DATE > T1.UPDATE_DATE)    \n");
        sqlStatCnt.append("  GROUP BY T2.STAGE_ID, T2.STAGE_NAME, T2.USER_ID, T2.FULL_NAME, T2.TRACK_ID, T2.CREATE_DATE \n");
        sqlStatCnt.append(") DIFF_TABLE  \n");
        sqlStatCnt.append("GROUP BY STAGE_ID, STAGE_NAME, USER_ID, FULL_NAME, TRACK_ID) TEMP2  \n");
        sqlStatCnt.append("ON TEMP1.STAGE_ID=TEMP2.STAGE_ID  \n");
        sqlStatCnt.append("AND TEMP1.USER_ID=TEMP2.USER_ID \n");
        sqlStatCnt.append("AND TEMP1.TRACK_ID=TEMP2.TRACK_ID \n");
        sqlStatCnt.append(") TEMP3  \n");
        sqlStatCnt.append("GROUP BY STAGE_ID, STAGE_NAME, USER_ID, FULL_NAME \n");
        log.debug(sqlStatCnt.toString());
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        i = 1;
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStatCnt.executeQuery();
        //      rs = preStatCnt.executeQuery();
        while (rs.next()) {
          String values[] = new String[]{getResultSetString(rs, "STAGE_NAME"),
              getResultSetString(rs, "FULL_NAME"),
              String.valueOf(getResultSetFloat(rs, "MIN_ALL")),
              String.valueOf(getResultSetFloat(rs, "MAX_ALL")),
              String.valueOf(getResultSetFloat(rs, "AVG_ALL")),
              String.valueOf(getResultSetFloat(rs, "MIN_ACTUAL")),
              String.valueOf(getResultSetFloat(rs, "MAX_ACTUAL")),
              String.valueOf(getResultSetFloat(rs, "AVG_ACTUAL"))
          };
          result.add(values);
        }
        
        sqlStat = new StringBuffer("DROP TABLE ##TMP_TABLE\n");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        try{ rows = preStat.executeUpdate(); }catch(SQLException ignore){}
        log.debug("##TMP_TABLE deleted = "+rows);
        
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  /**
   * Gets a list of workflow track by action taker for given searchForm
   * 
   * @param searchForm
   * @return list The String object
   * @throws ApplicationException
   */
  public synchronized List getWorkflowTrackListByActionTaker(AbstractSearchForm searchForm) throws ApplicationException {
    ListWorkflowMonitoringForm listWorkflowMonitoringForm = (ListWorkflowMonitoringForm)searchForm;
    String completeTrackIDs = this.getCompletedTracks(searchForm);
    if(Utility.isEmpty(completeTrackIDs)) return new ArrayList();
    
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    //    Form the WHERE clause for filtering.
    String searchDateFrom = null;
    String searchDateTo = null;
    String searchField = null;
    searchField = "P.UPDATE_DATE";
    searchDateFrom = listWorkflowMonitoringForm.getDatePeriodFrom();
    searchDateTo = listWorkflowMonitoringForm.getDatePeriodTo();
    synchronized(dbConn) {
      try {
        sqlStatCnt = new StringBuffer("SELECT T.ID, T.STAGE_NAME, U.FULL_NAME, T.DURATION, P.TRACK_ID, ");
        sqlStatCnt.append(" COUNT(P.ID) AS PROCESS_COUNT, MAX(P.UPDATE_DATE) AS MAX_UPDATE , MIN(P.CREATE_DATE) AS MIN_CREATE ");
        sqlStatCnt.append("FROM  WORKFLOW_PROGRESS P INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STEP S ON P.WORKFLOW_STEP_ID = S.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_STAGE_WF_STEP M ON S.ID = M.WORKFLOW_STEP_ID INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STAGE T ON M.WORKFLOW_STAGE_ID = T.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_PROGRESS_USER_RECORD PUR ON ");
        sqlStatCnt.append(" P.ID = PUR.WORKFLOW_PROGRESS_ID INNER JOIN ");
        sqlStatCnt.append(" USER_RECORD U ON PUR.USER_RECORD_ID = U.ID ");
        
        sqlStatCnt.append("WHERE S.RECORD_STATUS = ? ");
        sqlStatCnt.append("AND T.RECORD_STATUS = ? ");
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          sqlStatCnt.append("AND  S.WORKFLOW_RECORD_ID = ? ");
        }
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          sqlStatCnt.append(" AND ");
          if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= to_date('" + searchDateFrom+"', 'YYYY-MM-DD HH24:MI:SS') AND "+ searchField +" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= to_date('" + searchDateFrom+ "', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
          } else {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= '" + searchDateFrom+"' AND "+ searchField +" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= '" + searchDateFrom+ "') ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
          }
        }
        sqlStatCnt.append("   AND     P.TRACK_ID  IN (" + completeTrackIDs + ") ");
        // filtrate the end Step 
        sqlStatCnt.append("   AND     S.STEP_SEQ  < " + WorkflowStep.END_STEP_SEQ_NO  );
        sqlStatCnt.append(" GROUP BY  T.ID, T.STAGE_NAME, T.DURATION, U.FULL_NAME, P.TRACK_ID ");
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        log.debug ( sqlStatCnt.toString());
        
        int i = 1;
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          this.setPrepareStatement(preStatCnt, i++, listWorkflowMonitoringForm.getWorkflowRecordID());
        }
        rs = preStatCnt.executeQuery();
        
        while (rs.next()) {
          String currentTime = this.diffCompleteTimes(getResultSetTimestamp(rs, "MAX_UPDATE"), getResultSetTimestamp(rs, "MIN_CREATE"));
          String values[] = new String[]{String.valueOf(getResultSetInteger(rs, "TRACK_ID")),
              String.valueOf(getResultSetInteger(rs, "ID")),          
              getResultSetString(rs, "STAGE_NAME"),
              getResultSetString(rs, "FULL_NAME"),
              currentTime
          };
          result.add(values);
        }
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  /**
   * Gets a number of all workflow record duration's sum for given workflow record
   * 
   * @param workflowRecordID The workflow record ID
   * @return int
   * @throws ApplicationException
   */
  public int getWorkflowRecordDuratoin(Integer workflowRecordID) throws ApplicationException {
    int workflowRecordDuration = 0;
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    try {
      sqlStat.append("SELECT SUM(P.DURATION) ");
      sqlStat.append("FROM WORKFLOW_STAGE P  ");
      sqlStat.append("WHERE P.RECORD_STATUS = ?  ");
      sqlStat.append("AND P.WORKFLOW_RECORD_ID = ?  ");
      sqlStat.append("GROUP BY P.WORKFLOW_RECORD_ID ");
      preStat = dbConn.prepareStatement(sqlStat.toString());
      this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
      this.setPrepareStatement(preStat, 2, workflowRecordID);
      rs = preStat.executeQuery();
      if (rs.next()) {
        workflowRecordDuration = rs.getInt(1);
      }
      return (workflowRecordDuration);
    } catch (SQLException sqle) {
      log.error(sqle, sqle);
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } catch (Exception e) {
      log.error(e, e);
      throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
    } finally {
      try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
      try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
    }
  }
  /**
   * Gets a list of completed track for given searchForm
   * 
   * @param searchForm
   * @return list The String[] objects
   * @throws ApplicationException
   */
  public synchronized List getCompletedTrackList1(AbstractSearchForm searchForm) throws ApplicationException {
    ListWorkflowMonitoringForm listWorkflowMonitoringForm = (ListWorkflowMonitoringForm)searchForm;
    String completeTrackIDs = this.getCompletedTracks(searchForm);
    if(Utility.isEmpty(completeTrackIDs) && Utility.isEmpty(listWorkflowMonitoringForm.getTrackID())) return new ArrayList();
    
    int rows = 0;
    Statement stat = null;
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    String searchDateFrom = null;
    String searchDateTo = null;
    String searchField = null;
    searchField = "WP.UPDATE_DATE";
    searchDateFrom = listWorkflowMonitoringForm.getDatePeriodFrom();
    searchDateTo = listWorkflowMonitoringForm.getDatePeriodTo();
    synchronized(dbConn) {
      try {
        sqlStat = new StringBuffer("DROP TABLE ##TMP_TABLE\n");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        try{ rows = preStat.executeUpdate(); }catch(SQLException ignore){}
        log.debug("##TMP_TABLE deleted = "+rows);
        
        sqlStat = new StringBuffer();
        sqlStat.append("SELECT WP.ID, WP.TRACK_ID,WP.CREATE_DATE, ISNULL(WP.UPDATE_DATE, GETDATE()) AS UPDATE_DATE \n");
        sqlStat.append("INTO ##TMP_TABLE \n");
        sqlStat.append("FROM WORKFLOW_PROGRESS WP, WORKFLOW_STEP S, MTM_WF_STAGE_WF_STEP M, WORKFLOW_STAGE T \n");
        sqlStat.append("WHERE  WP.WORKFLOW_STEP_ID=S.ID \n");
        sqlStat.append("AND S.ID=M.WORKFLOW_STEP_ID \n");
        sqlStat.append("AND M.RECORD_STATUS=? \n");
        sqlStat.append("AND M.WORKFLOW_STAGE_ID=T.ID \n");
        //      sqlStat.append("AND WP.TRACK_ID = ? \n");
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          sqlStat.append("AND S.WORKFLOW_RECORD_ID = ? \n");
        }
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getTrackID())){
          sqlStat.append("AND WP.TRACK_ID =? \n");
        }
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          sqlStat.append(" AND ");
          if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
            sqlStat.append(" ("+ searchField +" >= ? AND "+ searchField +" < ?) ");
          else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
            sqlStat.append(" ("+searchField +" >= ?) ");
          else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
            sqlStat.append(" (" +searchField+" < ?) ");
        }
        if (Utility.isEmpty(listWorkflowMonitoringForm.getTrackID())){
          sqlStat.append("AND WP.COMPLETE_FLAG = ? ");
        }
        log.debug (sqlStat.toString());
        preStat = dbConn.prepareStatement(sqlStat.toString());
        
        int i = 1;
        this.setPrepareStatement(preStat, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          this.setPrepareStatement(preStat, i++, listWorkflowMonitoringForm.getWorkflowRecordID());
        }
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getTrackID())) {
          this.setPrepareStatement(preStat, i++, listWorkflowMonitoringForm.getTrackID());
        }
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) ) {
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(searchDateFrom));
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(TextUtility.dbDateAdder(searchDateTo,1)));
          } else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) ) {
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(searchDateFrom));
          } else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) ) {
            this.setPrepareStatement(preStat, i++, TextUtility.parseDBDateTimeToTimeStamp(TextUtility.dbDateAdder(searchDateTo,1)));
          }
        }
        if (Utility.isEmpty(listWorkflowMonitoringForm.getTrackID())){
          this.setPrepareStatement(preStat, i++, GlobalConstant.TRUE);
        }
        rows = preStat.executeUpdate();
        log.debug("Number rows in ##TMP_TABLE = "+rows);
        
        sqlStatCnt = new StringBuffer();
        sqlStatCnt.append("SELECT TEMP1.TRACK_ID, TEMP1.LARGEST_DIFF AS ALL_DURATION, TEMP2.HOLES_SUM, ISNULL(TEMP1.LARGEST_DIFF-TEMP2.HOLES_SUM, TEMP1.LARGEST_DIFF) AS ACTUAL_DURATION \n");
        sqlStatCnt.append("FROM \n");
        sqlStatCnt.append("(SELECT WP.TRACK_ID, DATEDIFF([SECOND], MIN(WP.CREATE_DATE), MAX(WP.UPDATE_DATE)) AS LARGEST_DIFF \n");
        sqlStatCnt.append("FROM ##TMP_TABLE WP \n");
        sqlStatCnt.append("GROUP BY WP.TRACK_ID) TEMP1 \n");
        sqlStatCnt.append("LEFT JOIN  \n");
        sqlStatCnt.append("(SELECT TRACK_ID, SUM(DATEDIFF([SECOND], HOLE_START, HOLE_END)) AS HOLES_SUM FROM ( \n");
        sqlStatCnt.append("  SELECT T2.TRACK_ID, MAX(T1.UPDATE_DATE) AS HOLE_START, T2.CREATE_DATE AS HOLE_END \n");
        sqlStatCnt.append("   FROM ##TMP_TABLE T1, ##TMP_TABLE T2  \n");
        sqlStatCnt.append("  WHERE T1.TRACK_ID=T2.TRACK_ID AND T1.ID <> T2.ID \n");
        sqlStatCnt.append("  AND NOT  EXISTS (SELECT 'X' FROM ##TMP_TABLE T3 WHERE T3.TRACK_ID=T2.TRACK_ID AND T3.UPDATE_DATE > T2.CREATE_DATE AND T3.CREATE_DATE < T1.UPDATE_DATE)  \n");
        sqlStatCnt.append("  AND NOT  EXISTS (SELECT 'X' FROM ##TMP_TABLE T4 WHERE T4.TRACK_ID=T2.TRACK_ID AND T4.CREATE_DATE < T2.CREATE_DATE AND T4.UPDATE_DATE > T1.UPDATE_DATE)  \n");
        sqlStatCnt.append("  GROUP BY T2.TRACK_ID, T2.CREATE_DATE \n");
        sqlStatCnt.append(") DIFF_TABLE \n");
        sqlStatCnt.append("GROUP BY TRACK_ID) TEMP2 \n");
        sqlStatCnt.append("ON TEMP1.TRACK_ID=TEMP2.TRACK_ID \n");
        log.debug(sqlStatCnt.toString());
        stat = dbConn.createStatement();
        rs = stat.executeQuery(sqlStatCnt.toString());
        //      rs = preStatCnt.executeQuery();
        while (rs.next()) {
          String values[] = new String[]{
              getResultSetInteger(rs, "TRACK_ID").toString(),
              String.valueOf(getResultSetInteger(rs, "ACTUAL_DURATION")),
              String.valueOf(getResultSetInteger(rs, "ALL_DURATION"))
          };
          result.add(values);
        }
        
        sqlStat = new StringBuffer("DROP TABLE ##TMP_TABLE\n");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        try{ rows = preStat.executeUpdate(); }catch(SQLException ignore){}
        log.debug("##TMP_TABLE deleted = "+rows);
        
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { stat.close(); } catch (Exception ignore) {} finally { stat = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Gets a list of completed track for given searchForm
   * 
   * @param searchForm
   * @return list The String objects
   * @throws ApplicationException
   */
  public synchronized List getCompletedTrackList(AbstractSearchForm searchForm) throws ApplicationException {
    /*
    ListWorkflowMonitoringForm listWorkflowMonitoringForm = (ListWorkflowMonitoringForm)searchForm;
    String completeTrackIDs = this.getCompletedTracks(searchForm);
    if(Utility.isEmpty(completeTrackIDs)) return new ArrayList();
    
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    String searchDateFrom = null;
    String searchDateTo = null;
    String searchField = null;
    searchField = "P.UPDATE_DATE";
    searchDateFrom = listWorkflowMonitoringForm.getDatePeriodFrom();
    searchDateTo = listWorkflowMonitoringForm.getDatePeriodTo();
    synchronized(dbConn) {
      List trackList = new ArrayList();
      try {
        sqlStatCnt = new StringBuffer("SELECT P.TRACK_ID, MAX(P.UPDATE_DATE) AS MAX_UPDATE,  MIN(P.CREATE_DATE) AS MIN_CREATE ");
        sqlStatCnt.append("FROM  WORKFLOW_PROGRESS P INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STEP S ON P.WORKFLOW_STEP_ID = S.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_STAGE_WF_STEP M ON S.ID = M.WORKFLOW_STEP_ID INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STAGE T ON M.WORKFLOW_STAGE_ID = T.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_STEP_USER_ACTOR A ON ");
        sqlStatCnt.append(" S.ID = A.WORKFLOW_STEP_ID  ");
        sqlStatCnt.append("WHERE S.RECORD_STATUS = ? ");
        sqlStatCnt.append(" AND T.RECORD_STATUS = ? ");
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          sqlStatCnt.append("AND  S.WORKFLOW_RECORD_ID = ? ");
        }
        if(!Utility.isEmpty(searchDateFrom) || !Utility.isEmpty(searchDateTo)){
          sqlStatCnt.append(" AND ");
          if (DataSourceFactory.getDatabaseType() == DataSourceFactory.DB_ORACLE) {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= to_date('" + searchDateFrom+"', 'YYYY-MM-DD HH24:MI:SS') AND "+ searchField +" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= to_date('" + searchDateFrom+ "', 'YYYY-MM-DD HH24:MI:SS') ) ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < to_date('"+TextUtility.dbDateAdder(searchDateTo,1)+"', 'YYYY-MM-DD HH24:MI:SS') ) ");
          } else {
            if((!Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+ searchField +" >= '" + searchDateFrom+"' AND "+ searchField +" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
            else if((!Utility.isEmpty(searchDateFrom)) && (Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" ("+searchField +" >= '" + searchDateFrom+ "') ");
            else if((Utility.isEmpty(searchDateFrom)) && (!Utility.isEmpty(searchDateTo)) )
              sqlStatCnt.append(" (" +searchField+" < '"+TextUtility.dbDateAdder(searchDateTo,1)+"') ");
          }
        }
        sqlStatCnt.append(" AND P.TRACK_ID  IN (" + completeTrackIDs + ") ");
        sqlStatCnt.append(" GROUP BY P.TRACK_ID ");
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        log.debug ( sqlStatCnt.toString());
        
        int i = 1;
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (!Utility.isEmpty(listWorkflowMonitoringForm.getWorkflowRecordID())) {
          this.setPrepareStatement(preStatCnt, i++, listWorkflowMonitoringForm.getWorkflowRecordID());
        }
        rs = preStatCnt.executeQuery();
        while (rs.next()) {
          String currentTime = this.diffCompleteTimes(getResultSetTimestamp(rs, "MAX_UPDATE"), getResultSetTimestamp(rs, "MIN_CREATE"));
          String[] currentExeTime = new String[2];
          currentExeTime[0] = getResultSetInteger(rs, "TRACK_ID").toString();
          currentExeTime[1] = currentTime;
          //trackForExeTimeList.add(currentExeTime);
          result.add(currentExeTime);
        }  
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
    */

    String completeTrackIDs = this.getCompletedTracks(searchForm);
    PreparedStatement preStatCnt = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    List trackForExeTimeList  = new ArrayList();
    if(Utility.isEmpty(completeTrackIDs)) {
      return trackForExeTimeList;
    }
    synchronized(dbConn) {
      List trackList = new ArrayList();
      try {
        sqlStatCnt.append("  SELECT P.TRACK_ID, MAX(P.UPDATE_DATE) AS MAX_UPDATE , MIN(P.CREATE_DATE) AS MIN_CREATE \n");
        sqlStatCnt.append("FROM  WORKFLOW_PROGRESS P \n");
        sqlStatCnt.append(" WHERE   P.TRACK_ID  IN (" + completeTrackIDs + ") ");
        sqlStatCnt.append("  GROUP BY P.TRACK_ID \n");
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        rsCnt = preStatCnt.executeQuery();
        while (rsCnt.next()) {
          String currentTime = this.diffCompleteTimes(getResultSetTimestamp(rsCnt, "MAX_UPDATE"), getResultSetTimestamp(rsCnt, "MIN_CREATE"));
          String[] currentExeTime = new String[2];
          currentExeTime[0] = getResultSetInteger(rsCnt, "TRACK_ID").toString();
          currentExeTime[1] = currentTime;
          trackForExeTimeList.add(currentExeTime);
        }
        log.debug ( sqlStatCnt.toString());
        log.debug ( "getCompletedTrackNumber = " + trackForExeTimeList.size() );
        return(trackForExeTimeList);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  
  /**
   * Gets a list of workflow track info by action taker for given workflow track
   * 
   * @param trackID The workflow track ID
   * @return list The String[] objects
   * @throws ApplicationException
   */
  public synchronized List getWorkflowTrackInfoListByActionTaker(Integer trackID) throws ApplicationException {
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    String trackIDs = "-1";
    int totalNumOfRecord = 0;
    List result = new ArrayList();
    //   Form the WHERE clause for filtering.
    
    synchronized(dbConn) {
      try {
        sqlStatCnt = new StringBuffer("SELECT T.ID AS STAGE_ID , T.STAGE_NAME, U.FULL_NAME, U.ID AS USER_ID, T.DURATION, P.TRACK_ID,  COUNT(P.ID) AS PROCESS_COUNT, MIN(P.CREATE_DATE) AS START_DATE, MAX(P.UPDATE_DATE) AS END_DATE ");
        sqlStatCnt.append("FROM  WORKFLOW_PROGRESS P INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STEP S ON P.WORKFLOW_STEP_ID = S.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_STAGE_WF_STEP M ON S.ID = M.WORKFLOW_STEP_ID INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STAGE T ON M.WORKFLOW_STAGE_ID = T.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_PROGRESS_USER_RECORD PUR ON ");
        sqlStatCnt.append(" P.ID = PUR.WORKFLOW_PROGRESS_ID INNER JOIN ");
        sqlStatCnt.append(" USER_RECORD U ON PUR.USER_RECORD_ID = U.ID ");
        sqlStatCnt.append("WHERE S.RECORD_STATUS = ? ");
        sqlStatCnt.append("AND T.RECORD_STATUS = ? ");
        sqlStatCnt.append("AND P.TRACK_ID = ? ");
        sqlStatCnt.append(" GROUP BY  P.TRACK_ID, T.ID, T.STAGE_NAME, T.DURATION, U.FULL_NAME , U.ID ");
        sqlStatCnt.append(" ORDER BY  P.TRACK_ID, T.ID ");
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        int i = 1;
        
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, trackID);
        rs = preStatCnt.executeQuery();
        while (rs.next()) {
          
          String values[] = new String[]{getResultSetString(rs, "STAGE_NAME"),
              getResultSetString(rs, "FULL_NAME"),
              String.valueOf(getResultSetInteger(rs, "DURATION")),
              String.valueOf(getResultSetTimestamp(rs, "START_DATE")),
              String.valueOf(getResultSetTimestamp(rs, "END_DATE")),
              String.valueOf(getResultSetInteger(rs, "STAGE_ID")),
              String.valueOf(getResultSetInteger(rs, "USER_ID")),
          };
          result.add(values);
        }
        log.debug ( sqlStatCnt.toString());
        log.debug ( "getStoppedWorkflowTrackNumber = " + result.size() );
        
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  /**
   * Gets a number of current workflow executive time for given workflow track 
   * 
   * @param trackID The workflow track ID
   * @return long
   * @throws ApplicationException
   */
  public synchronized long getCurrentExeTimeForWorkflowTrack(Integer trackID) throws ApplicationException {
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    String trackIDs = "-1";
    int totalNumOfRecord = 0;
    //     Form the WHERE clause for filtering.
    long result = (long) 0.0;
    synchronized(dbConn) {
      try {
        sqlStatCnt = new StringBuffer("SELECT P.TRACK_ID,  COUNT(P.ID) AS PROCESS_COUNT, MIN(P.CREATE_DATE) AS START_DATE, MAX(P.UPDATE_DATE) AS END_DATE ");
        sqlStatCnt.append("FROM  WORKFLOW_PROGRESS P  ");
        sqlStatCnt.append("WHERE P.RECORD_STATUS = ? ");
        sqlStatCnt.append("AND P.TRACK_ID = ? ");
        sqlStatCnt.append(" GROUP BY  P.TRACK_ID ");
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        int i = 1;
        
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, trackID);
        rs = preStatCnt.executeQuery();
        if (rs.next()) {
          long startDate = getResultSetTimestamp(rs, "START_DATE").getTime();
          long endDate = getResultSetTimestamp(rs, "END_DATE").getTime();
          result = endDate - startDate;
        }
        log.debug ( sqlStatCnt.toString());
        log.debug ( "result = " + result );
        
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  /**
   * Gets a list of completed track for given workflow track
   * 
   * @param trackID The workflow track ID
   * @return list The String objects
   * @throws ApplicationException
   */
  public synchronized List  getTrackExeTimeListByTrackID(Integer trackID) throws ApplicationException {
    ListWorkflowMonitoringForm searchForm = new ListWorkflowMonitoringForm();
    searchForm.setTrackID(String.valueOf(trackID));
    List resultList = getCompletedTrackList(searchForm);
    return resultList;
  }
  
  public synchronized Map getPendingProgressCountByActionTaker(Integer trackID) throws ApplicationException {
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    String trackIDs = "-1";
    int totalNumOfRecord = 0;
    Map result = new HashMap();
    //     Form the WHERE clause for filtering.
    
    synchronized(dbConn) {
      try {
        sqlStatCnt = new StringBuffer("SELECT T.ID, T.STAGE_NAME, T.DURATION, P.TRACK_ID, COUNT(P.ID) AS PROCESS_COUNT, MIN(P.CREATE_DATE) AS START_DATE, MAX(P.UPDATE_DATE) AS END_DATE ");
        sqlStatCnt.append("FROM  WORKFLOW_PROGRESS P INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STEP S ON P.WORKFLOW_STEP_ID = S.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_STAGE_WF_STEP M ON S.ID = M.WORKFLOW_STEP_ID INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STAGE T ON M.WORKFLOW_STAGE_ID = T.ID  ");
        sqlStatCnt.append("WHERE S.RECORD_STATUS = ? ");
        sqlStatCnt.append("AND T.RECORD_STATUS = ? ");
        sqlStatCnt.append("AND (P.STATUS = ? OR P.STATUS = ? OR P.STATUS = ? ) ");
        sqlStatCnt.append("AND P.TRACK_ID = ? ");
        sqlStatCnt.append(" GROUP BY  P.TRACK_ID, T.ID, T.STAGE_NAME, T.DURATION ");
        sqlStatCnt.append(" ORDER BY  P.TRACK_ID, T.ID ");
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        int i = 1;
        
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, WorkflowProgress.STATUS_IN_PROGRESS);
        this.setPrepareStatement(preStatCnt, i++, WorkflowProgress.STATUS_LOCK);
        this.setPrepareStatement(preStatCnt, i++, WorkflowProgress.STATUS_PENDING);
        this.setPrepareStatement(preStatCnt, i++, trackID);
        rs = preStatCnt.executeQuery();
        while (rs.next()) {
          result.put(getResultSetString(rs, "STAGE_NAME"),String.valueOf(getResultSetInteger(rs, "PROCESS_COUNT")));
        }
        log.debug ( sqlStatCnt.toString());
        log.debug ( "getStoppedWorkflowTrackNumber = " + result.size() );
        
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  /**
   * Gets a list by  stageID for given workflow track and given workflow stage
   * 
   * @param trackID The workflow track ID
   * @param stageID The workflow stage ID
   * @return list The MtmWorkflowProgressUserRecord object
   * @throws ApplicationException
   */
  public synchronized List getMtmWfUserRecordInfoByStageID(Integer trackID, Integer stageID) throws ApplicationException {
    
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    StringBuffer sqlStatCnt = new StringBuffer();
    String trackIDs = "-1";
    int totalNumOfRecord = 0;
    List result = new ArrayList();
    //    Form the WHERE clause for filtering.
    
    synchronized(dbConn) {
      try {
        sqlStatCnt = new StringBuffer("SELECT PUR.ID, PUR.WORKFLOW_PROGRESS_ID, PUR.USER_RECORD_ID, PUR.REASON, PUR.RECORD_STATUS, PUR.UPDATE_COUNT,PUR.CREATOR_ID,PUR.CREATE_DATE, PUR.UPDATER_ID, PUR.UPDATE_DATE ");
        sqlStatCnt.append("FROM  WORKFLOW_PROGRESS P INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STEP S ON P.WORKFLOW_STEP_ID = S.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_STAGE_WF_STEP M ON S.ID = M.WORKFLOW_STEP_ID INNER JOIN ");
        sqlStatCnt.append(" WORKFLOW_STAGE T ON M.WORKFLOW_STAGE_ID = T.ID INNER JOIN ");
        sqlStatCnt.append(" MTM_WF_PROGRESS_USER_RECORD PUR ON ");
        sqlStatCnt.append(" P.ID = PUR.WORKFLOW_PROGRESS_ID INNER JOIN ");
        sqlStatCnt.append(" USER_RECORD U ON PUR.USER_RECORD_ID = U.ID ");
        sqlStatCnt.append("WHERE S.RECORD_STATUS = ? ");
        sqlStatCnt.append("AND T.RECORD_STATUS = ? ");
        sqlStatCnt.append("AND P.TRACK_ID = ? ");
        sqlStatCnt.append("AND PUR.WORKFLOW_ACTION <> ? ");
        sqlStatCnt.append("AND PUR.WORKFLOW_ACTION <> ? ");
        sqlStatCnt.append("AND PUR.WORKFLOW_ACTION <> ? ");
        
        sqlStatCnt.append("AND T.ID = ? ");
        sqlStatCnt.append(" ORDER BY  PUR.ID ");
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        int i = 1;
        
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStatCnt, i++, trackID);
        this.setPrepareStatement(preStatCnt, i++, WorkflowProgress.STATUS_IN_PROGRESS);
        this.setPrepareStatement(preStatCnt, i++, WorkflowProgress.STATUS_LOCK);
        this.setPrepareStatement(preStatCnt, i++, WorkflowProgress.STATUS_PENDING);
        this.setPrepareStatement(preStatCnt, i++, stageID);
        rs = preStatCnt.executeQuery();
        while (rs.next()) {
          MtmWorkflowProgressUserRecord tmpMtmWorkflowProgressUserRecord = new MtmWorkflowProgressUserRecord();
          tmpMtmWorkflowProgressUserRecord.setID(getResultSetInteger(rs, "ID"));
          tmpMtmWorkflowProgressUserRecord.setWorkflowProgressID(getResultSetInteger(rs, "WORKFLOW_PROGRESS_ID"));
          tmpMtmWorkflowProgressUserRecord.setUserRecordID(getResultSetInteger(rs, "USER_RECORD_ID"));
          tmpMtmWorkflowProgressUserRecord.setReason(getResultSetString(rs, "REASON"));
          tmpMtmWorkflowProgressUserRecord.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpMtmWorkflowProgressUserRecord.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpMtmWorkflowProgressUserRecord.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpMtmWorkflowProgressUserRecord.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpMtmWorkflowProgressUserRecord.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpMtmWorkflowProgressUserRecord.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpMtmWorkflowProgressUserRecord.setCreatorName(UserInfoFactory.getUserFullName(tmpMtmWorkflowProgressUserRecord.getCreatorID()));
          tmpMtmWorkflowProgressUserRecord.setUpdaterName(UserInfoFactory.getUserFullName(tmpMtmWorkflowProgressUserRecord.getUpdaterID()));
          result.add(tmpMtmWorkflowProgressUserRecord);
        }
        log.debug ( sqlStatCnt.toString());
        log.debug ( "getStoppedWorkflowTrackNumber = " + result.size() );
        
        return(result);
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  } 
  private String diffCompleteTimes(Timestamp updateDate, Timestamp createDate){
    long diffMillisecondTime = updateDate.getTime() - createDate.getTime();
    return (new Long(diffMillisecondTime/1000).toString());
  }
  
}